﻿

CREATE PROCEDURE [dbo].[fares_GenereazaJurnalCumparari] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime
AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
docd.*
FROM 
Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
JOIN 

	(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
	SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19)  THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
	SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19) THEN ValoareTVA ELSE 0 END) TVA24,
	SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
	SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
	SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) InversBaza,
	SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN ValoareTVA ELSE 0 END) InversTVA,
	SUM(CASE WHEN dd.IDRegimFiscal IN (20) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) ImportTaraScutite,
	SUM(CASE WHEN dd.IDRegimFiscal IN (27) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ObligatBaza,
	SUM(CASE WHEN dd.IDRegimFiscal IN (27) THEN ValoareTVA ELSE 0 END) ObligatTVA,
	SUM(CASE WHEN dd.IDRegimFiscal IN (29) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCScutit,
	SUM(CASE WHEN dd.IDRegimFiscal IN (22) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) DRScutit,
	SUM(CASE WHEN dd.IDRegimFiscal IN (24) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) NFScutit,
	SUM(CASE WHEN dd.IDRegimFiscal IN (30) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCNeimp,
	SUM(CASE WHEN dd.IDRegimFiscal IN (23) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) DRNeimp,
	SUM(CASE WHEN dd.IDRegimFiscal IN (26) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) NFNeimp,
	SUM(CASE WHEN dd.IDRegimFiscal IN (28) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCBI,
	SUM(CASE WHEN dd.IDRegimFiscal IN (21) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) DRBI,
	SUM(CASE WHEN dd.IDRegimFiscal IN (24) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) NFBI,
	SUM(CASE WHEN dd.IDRegimFiscal IN (28) THEN ValoareTVA ELSE 0 END) BCTVA,
	SUM(CASE WHEN dd.IDRegimFiscal IN (21) THEN ValoareTVA ELSE 0 END) DRTVA,
	SUM(CASE WHEN dd.IDRegimFiscal IN (24) THEN ValoareTVA ELSE 0 END) NFTVA

	FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
	GROUP BY dd.IDDocument) docd ON doc.IDDocument = docd.IDDocument
	
	
	WHERE tipd.IDTipDocument in (0,10) AND doc.CodUnitate=@CodUnitate AND 
	doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData AND LTRIM(RTRIM(part.AtributFiscal))<>''
	ORDER BY DataDoc
END